package com.nccsjz.back.game.dao;

import java.io.File;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.nccsjz.pojo.Game;
import com.nccsjz.pojo.Resources;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.utils.Pager;
import com.nccsjz.base.BaseDAO;

public class GameDAO extends BaseDAO {
	boolean flag = false;

	public GameDAO(Connection con) {
		super(con);
	}

	// add game
	public boolean addGame(Game game) throws SQLException {
		try {
			String sql = "insert into game (gametitle,gamepath,uploaddate,gamedesc,gamepic) values(?,?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, game.getGametitle());
			pstmt.setString(2, game.getGamepath());
			pstmt.setDate(3, DateUtil.convertDate(new java.util.Date()));
			pstmt.setString(4, game.getGamedesc());
			pstmt.setString(5, game.getGamepic());
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// delete game
	public boolean deleteGame(Long id) throws SQLException {
		try {
			String sql = "delete from game where gameid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}


	// batch delete games
	public boolean deleteBatchGame(List<Long> list) throws SQLException {
		int num[] = null;
		try {
			String sql = "delete from game where gameid=?";
			pstmt = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				pstmt.setLong(1, list.get(i));
				pstmt.addBatch();
			}
			num = pstmt.executeBatch();
			if (num != null) {
				flag = true;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);

		}
		return flag;
	}

	// get one game
	public Game getGame(Long id) throws SQLException {
		Game game = null;
		try {
			String sql = "select * from game where gameid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				game = new Game();
				game.setGameid(rs.getLong("gameid"));
				game.setGametitle(rs.getString("gametitle"));
				game.setGamepath(rs.getString("gamepath"));
				game.setGamedesc(rs.getString("gamedesc"));
				game.setUploaddate(rs.getDate("uploaddate"));
				game.setGamepic(rs.getString("gamepic"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			closeAll(rs, pstmt);
		}
		return game;
	}

	// pager display resources
	public Pager getGame(int pageSize, int pageNo) throws SQLException {
		List<Game> list = new ArrayList<Game>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from game";
			//String sql2 = "select * from(select u.*,rownum rn from(select * from game order by uploaddate desc) u where rownum<=?)where rn>?";
			String sql2 = "select * from (select game.*, row_number() over(order by gameid desc) rn " 
				+ "from game) u where u.rn > ? and u.rn <= ?";			
			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, (pageNo - 1) * pageSize);
			pstmt.setInt(2, pageNo * pageSize);			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Game game = new Game();
				game.setGameid(rs.getLong("gameid"));
				game.setGametitle(rs.getString("gametitle"));
				game.setGamepath(rs.getString("gamepath"));
				game.setGamedesc(rs.getString("gamedesc"));
				game.setUploaddate(rs.getDate("uploaddate"));
				game.setGamepic(rs.getString("gamepic"));
				list.add(game);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}

	// select game by title
	public Pager getGame(int pageSize, int pageNo, String title)
			throws SQLException {
		List<Game> list = new ArrayList<Game>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from game ";
			//String sql2 = "select * from(select u.*,rownum rn from(select * from game  where gametitle like ? order by uploaddate desc) u where rownum<=?)where rn>?";
			
			String sql2 = "select * from (select game.*, row_number() over(order by gameid desc) rn from game "; 
					
			
			boolean flag = StringUtils.isNotBlank(title);
			String ctitle = null;
			if(flag){
				ctitle = "%" + title + "%";
				sql1 += " where gametitle like ? ";
				sql2 += " where gametitle like ? ";
			}
			
			sql2 += " ) u where u.rn > ? and u.rn <= ?";
			pstmt = con.prepareStatement(sql1);
			if(flag){
				pstmt.setString(1, ctitle);
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			if(flag){
				pstmt.setString(1, ctitle);
				pstmt.setInt(2, (pageNo - 1) * pageSize);
				pstmt.setInt(3, pageNo * pageSize);				
			}else{
				pstmt.setInt(1, (pageNo - 1) * pageSize);
				pstmt.setInt(2, pageNo * pageSize);
			}
			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Game game = new Game();
				game.setGameid(rs.getLong("gameid"));
				game.setGametitle(rs.getString("gametitle"));
				game.setGamepath(rs.getString("gamepath"));
				game.setGamedesc(rs.getString("gamedesc"));
				game.setUploaddate(rs.getDate("uploaddate"));
				game.setGamepic(rs.getString("gamepic"));
				list.add(game);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}
}
